package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.szholly.data.general.*;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.utils.spring.SpringBeanFactory;
import org.springframework.stereotype.Service;


/**
 * 设备故障及维修服务
 *
 */
@Service
public class FailureRepairService {

	/**
	 * 获取设备维修记录
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getFailureRepairList(Map<String, String> params, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = " select f.*, t1.NAME as WWTP_NAME, t2.NAME as STATION_NAME, t2.FLOW_NAME as FLOW_NAME, t3.NAME as EQUIPMENT_NAME, t3.DEVICE_TYPENUM, t4.c_userealname as USER_NAME,  "
						+ " d1.S_DOMAINCAPTION as FAILURE_REASON_NAME, d2.S_DOMAINCAPTION as REPAIR_RESULT_NAME, d3.S_DOMAINCAPTION as DEVICE_STATUS_NAME "
						+ " from OP_FAILURE_REPAIR f "
						+ " left join OPERATION_TASK t on ( t.TASK_CODE = f.TASK_ID ) "	
						+ " left join BC_SITE_INFO t2 on ( t2.EXP_NO = t.STATION_ID ) "
						+ " left join BC_ENTERPRISE_ATTR t1 on ( t1.EXP_NO = t2.WWTP_ID ) "
						+ " left join BC_SITE_DEVICE_RPT  t3 on ( t3.DEVICE_NUM = f.DEVICE_NUMBER ) "
						+ " left join safe_user t4 on ( t4.s_userid = f.REPAIR_PERSON ) "
						+ " left join DATA_DEFULATDOMAIN d1 on ( d1.S_DOMAINNAME = 'OPERATION_FAILURE_REASON' AND d1.S_DOMAINCODE = f.FAILURE_REASON ) "
						+ " left join DATA_DEFULATDOMAIN d2 on ( d2.S_DOMAINNAME = 'OPERATION_REPAIR_RESULT' AND d2.S_DOMAINCODE = f.REPAIR_RESULT ) "
						+ " left join DATA_DEFULATDOMAIN d3 on ( d3.S_DOMAINNAME = 'MONITOR_EQUIPMENT_STATUS' AND d3.S_DOMAINCODE = f.DEVICE_STATUS ) "
						+ " where 1=1 ";

		if (params.containsKey("task_code")) {
			sql += " and f.TASK_ID = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} else {
			if (params.containsKey("wwtp_id")) {
				sql += " and t2.WWTP_ID = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}

			if (params.containsKey("flow_type")) {
				sql += " and t2.FLOW_TYPE = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			
			if (params.containsKey("water_type")) {
				sql += " and t2.STATION_CATEGORY = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			
			
			
			if(params.containsKey("device_num")){
				sql += " and f.DEVICE_NUMBER =@device_num";
				listParam.add(new GlobalParameter("device_num", params.get("device_num")));
			}
			
			if (params.containsKey("station_id")) {
				sql += " and f.STATION_ID =@station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}

			if (params.containsKey("device_status")) {
				sql += " and f.DEVICE_STATUS in ("+params.get("device_status")+")";
			}
			
			if (params.containsKey("failure_reason")) {
				sql += " and f.FAILURE_REASON in ("+params.get("failure_reason")+")";
			}
			
			if (params.containsKey("repaire_result")) {
				sql += " and f.REPAIR_RESULT in ("+params.get("repaire_result")+")";
			}

			if (params.containsKey("start")) {
				sql += " and f.BEGIN_TIME >= to_date('" + params.get("start")
						+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and f.BEGIN_TIME <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		}

		sql += " order by f.BEGIN_TIME DESC";
		
		return DataUtil.getPageRow(sql, listParam, null,pageInfo);
	}
	
	/**
	 * 获取设备维修记录(地表水)by熊伟
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getSWFailureRepairList(Map<String, String> params, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = " select f.*, t1.NAME as WWTP_NAME, t2.NAME as STATION_NAME, t2.FLOW_NAME as FLOW_NAME, t3.NAME as EQUIPMENT_NAME, t3.DEVICE_TYPENUM,t3.DEVICE_NUM, t4.c_userealname as USER_NAME,  "
						+ " d1.S_DOMAINCAPTION as FAILURE_REASON_NAME, d2.S_DOMAINCAPTION as REPAIR_RESULT_NAME, d3.S_DOMAINCAPTION as DEVICE_STATUS_NAME "
						+ " from OP_FAILURE_REPAIR f "
						+ " left join OPERATION_TASK t on ( t.TASK_CODE = f.TASK_ID ) "	
						+ " left join BC_SITE_INFO t2 on ( t2.EXP_NO = t.STATION_ID ) "
						+ " left join BC_ENTERPRISE_ATTR t1 on ( t1.EXP_NO = t2.WWTP_ID ) "
						+ " left join BC_SITE_DEVICE_RPT  t3 on ( t3.DEVICE_NUM = f.DEVICE_NUMBER ) "
						+ " left join safe_user t4 on ( t4.s_userid = f.REPAIR_PERSON ) "
						+ " left join DATA_DEFULATDOMAIN d1 on ( d1.S_DOMAINNAME = '地表水故障类型' AND d1.S_DOMAINCODE = f.FAILURE_REASON ) "
						+ " left join DATA_DEFULATDOMAIN d2 on ( d2.S_DOMAINNAME = 'OPERATION_REPAIR_RESULT' AND d2.S_DOMAINCODE = f.REPAIR_RESULT ) "
						+ " left join DATA_DEFULATDOMAIN d3 on ( d3.S_DOMAINNAME = 'MONITOR_EQUIPMENT_STATUS' AND d3.S_DOMAINCODE = f.DEVICE_STATUS ) "
						+ " where 1=1 ";

		if (params.containsKey("task_code")) {
			sql += " and f.TASK_ID = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} else {
			if (params.containsKey("wwtp_id")) {
				sql += " and t2.WWTP_ID = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}

			if (params.containsKey("flow_type")) {
				sql += " and t2.FLOW_TYPE = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			
			if (params.containsKey("water_type")) {
				sql += " and t2.STATION_CATEGORY = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			
			
			
			if(params.containsKey("device_num")){
				sql += " and f.DEVICE_NUMBER =@device_num";
				listParam.add(new GlobalParameter("device_num", params.get("device_num")));
			}
			
			if (params.containsKey("station_id")) {
				sql += " and f.STATION_ID =@station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}

			if (params.containsKey("device_status")) {
				sql += " and f.DEVICE_STATUS in ("+params.get("device_status")+")";
			}
			
			if (params.containsKey("failure_reason")) {
				sql += " and f.FAILURE_REASON in ("+params.get("failure_reason")+")";
			}
			
			if (params.containsKey("repaire_result")) {
				sql += " and f.REPAIR_RESULT in ("+params.get("repaire_result")+")";
			}

			if (params.containsKey("start")) {
				sql += " and f.BEGIN_TIME >= to_date('" + params.get("start")
						+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and f.BEGIN_TIME <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		}

		sql += " order by f.BEGIN_TIME DESC";
		
		return DataUtil.getPageRow(sql, listParam, null,pageInfo);
	}
	
	/**
	 * 获取故障维修任务数据记录列表
	 * 
	 * @param params
	 * @return
	 */
	public List<RowBase> getFailureRepairDataList(Map<String, String> params) {
		if (params == null)
			return null;

		String strSTATION_ID = params.get("STATION_ID");
		String strJOB_ID = params.get("JOB_ID");
		String strSTART_TIME = params.get("START_TIME");
		String strEND_TIME = params.get("END_TIME");
		if (strSTATION_ID.length() < 1 || strJOB_ID.length() < 1)
			return null;

		String strSQL = " select t.TASK_CODE, t.TASK_STATUS, t.START_TIME as TASK_START_TIME, t.END_TIME as TASK_END_TIME, t.FINISH_TIME as TASK_FINISH_TIME,  \n"
				+ " t1.*, \n" + " t2.NAME as STATION_NAME, \n" + " t3.GROUP_NAME as GROUP_NAME, \n"
				+ " t4.C_USEREALNAME as USER_NAME \n" + " from OP_TASK t \n"
				+ " inner join OP_FAILURE_REPAIR t1 ON t1.TASK_ID = t.TASK_CODE \n" // dif
				+ " left join BC_SITE_INFO t2 on t2.EXP_NO = t.STATION_ID \n"
				+ " left join OC_GROUP t3 on t3.GUID = t.OPERATION_GROUP_ID \n"
				+ " left join SAFE_USER t4 on t4.S_USERID = t.FINISH_BY \n" + " where t.STATION_ID = '" + strSTATION_ID
				+ "' \n" + " AND t.JOB_ID = '" + strJOB_ID + "' \n"
				// + " t1.PARAM = '" + strPARAM_ID + "' AND \n" //dif
				+ " AND t.START_TIME >= to_date('" + strSTART_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " AND t.START_TIME <= to_date('" + strEND_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " order by t.START_TIME desc ";

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		List<RowBase> rcdList = DataUtil.getSelectRows(strSQL, null, pTable);
		return rcdList;
	}
	
	/**
	 * 获取站点在线监测设备故障记录
	 * 
	 * @param params
	 * @return
	 */
	public List<RowBase> getStationDeviceFailureRcdList(Map<String, String> params) {

		String strSTATION_ID = params.get("STATION_ID");
		String strSTART_TIME = params.get("START_TIME");
		String strEND_TIME = params.get("END_TIME");

		if (StringUtil.IsNullOrEmpty(strSTATION_ID))
			return null;

		String strSQL = " select t1.* " 
				+ " from OP_FAILURE_REPAIR t1 "
				+ " left join OPERATION_TASK t2 on t2.TASK_CODE = t1.TASK_ID " 
				+ " where t2.STATION_ID = @STATION_ID"
				+ " AND t1.BEGIN_TIME >= to_date('" + strSTART_TIME	+ " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') " 
				+ " AND t1.BEGIN_TIME <= to_date('" + strEND_TIME	+ " 23:59:59', 'yyyy-mm-dd hh24:mi:ss') ";

		List<GlobalParameter> listParam = new ArrayList<>();
		listParam.add(new GlobalParameter("STATION_ID",strSTATION_ID));
		List<RowBase> rcdList = DataUtil.getSelectRows(strSQL, listParam);
		if (rcdList.size() < 1)
			return null;
		
		return rcdList;
	}
}
